a)

import java.sql.*;

char model[4], maker;
float minSpeed;
int minRam, minHd, minScreen;
float speed;
int ram, hd, screen,price;

Class.forName(“<drive name>”);

Connection myCon = 
          DriverManager.getConnection(<URL>, <username>, <password>);

/* ask user for maximum price, minimum speed, ram, hd size, and screen size */

PreparedStatement execStat = myCon.prepareStatement(
             “SELECT model, speed, ram, hd, screen, price, maker ” +
               “FROM Laptop l, Product p ” +
                  “WHERE price <= ? AND ” +
						"speed >= ? AND ” +
                        “ram >= ? AND ” +
                        “hd >= ? AND ” +
                        “screen >= ? AND ” +
                        “l.model = p.model”);

execStat.setFloat(1, minSpeed);
execStat.setInt(2, minRam);
execStat.setInt(3, minHd);
execStat.setInt(4, minScreen);
execStat.setInt(5, maxPrice);

ResultSet products = execStat.executeQuery();

While(products.next()) {

    model = getString(1);
    speed = getFloat(2);
    ram = getInt(3);
    hd = getInt(4);
    screen = getInt(5);
    price = getInt(6);
    maker = getString(7);

    /* print fetched info */
}
--------------------------------------------------------------------------------
b)

import java.sql.*;

char pmodel[4], pmaker, ptype[6];
float pspeed;
int pram, phd, pscreen, pprice, count;

char maker, model[4], type[10], color[6];
float speed;
int ram, hd, screen, price;


Class.forName(“<drive name>”);

Connection myCon = 
          DriverManager.getConnection(<URL>, <username>, <password>);


/* ask user for manufacturer, model, speed, RAM, hard-disk, */
/* & price of a new PC */

PreparedStatement execStat = myCon.prepareStatement( 
             “SELECT COUNT(*) FROM PC WHERE model = ?”);
execStat.setString(1, pmodel);

ResultSet rs = execStat.executeQuery();

Count = rs.getInt(1);

if (count  > 0) {
      Printf(“Warnning: The PC model already exists\n”);
}
else {		

   PreparedStatement execStat2 = myCon.prepareStatement(
                       “INSERT INTO Product VALUES(?, ?, ?)”);

   execStat2.setString(1, pmaker);
   execStat2.setString(2, pmodel);
   execStat2.setString(3, ptype);
   execStat2.executeUpdate();


   PreparedStatement execStat3 = myCon.prepareStatement(
                    “INSERT INTO PC VALUES(?, ?, ?, ?, ?)”);

   execStat3.setString(1, pmodel);
   execStat3.setFloat(2, pspeed);
   execStat3.setInt(3, pram);
   execStat3.setInt(4, phd);
   execStat3.setInt(5, pprice);
   execStat3.executeUpdate();

}
--------------------------------------------------------------------------------
c)

import java.sql.*;

char manf, tempModel[4];
float tempSpeed;
int tempPrice;

Int targetPrice;
char modelOfClosest[4];
float speedOfClosest;
int priceOfClosest;


Class.forName(“<drive name>”);

Connection myCon = 
          DriverManager.getConnection(<URL>, <username>, <password>);


/* ask user for target price and read the answer into variable
       targetPrice */

PreparedStatement execStat = myCon.prepareStatement(
                  “SELECT model, price, speed FROM PC”);
ResultSet pcs = execStat.executeQuery();

While(pcs.next()) {

    tempModel = pcs.getString(1);
    tempPrice = pcs.getInt(2);
    tempSpeed = pcs.getFloat(3);

    if( /* the 1st fetch or tempPrice closer to targetPrice */ ) {
        modelOfClosest = tempModel;
        priceOfClosest = tempPrice;
        speedOfClosest = tempSpeed; 
    }

}

/* Now, modelOfClosest is the model whose price is closest to
       target.  We must get its manufacturer with a single-row select */

if (priceOfClosest == NULL ) /* no data fetched */
  /* print error message and exit */  

PreparedStatement execStat2 = myCon.prepareStatement(
                  “SELECT maker FROM Product WHERE model = ?”);
execStat2.setString(1, modelOfClosest);

ResultSet makers = execStat2.executeQuery();

/* print manf */
--------------------------------------------------------------------------------
d)

import java.sql.*;

char maker, model[4], type[10], color[6];
float speed;
int ram, hd, screen, price;


Class.forName(“<drive name>”);

Connection myCon = 
          DriverManager.getConnection(<URL>, <username>, <password>);

/* ask user for manufacturer */


/* get PCs made by the manufacturer */

PreparedStatement execStat = myCon.prepareStatement(
             “SELECT * FROM PC WHERE model IN (” +
                             “SELECT model FROM Product ” +
                                 “WHERE maker = ? AND ” +
                                       “type = ‘pc’”);
execStat.setString(1, maker);

ResultSet pcs = execStat.executeQuery();

while (pcs.next()) {

   model = pcs.getString(1);
   speed = pcs.getFloat(2);
   ram = pcs.getInt(3);
   hd = pcs.getInt(4);
   price = pcs.getInt(5);

   /* print fetched info */

}

/* get Laptops made by the manufacturer */

PreparedStatement execStat2 = myCon.prepareStatement(
             “SELECT * FROM Laptop WHERE model IN (” +
                             “SELECT model FROM Product ” +
                                 “WHERE maker = ? AND ” +
                                       “type = ‘laptop’”);

execStat.setString(1, maker);

ResultSet laptops = execStat2.executeQuery();

while (laptops.next()) {

   model = laptops.getString(1);
   speed = laptops.getFloat(2);
   ram = laptops.getInt(3);
   hd = laptops.getInt(4);
   screen = laptops.getInt(5);
   price = laptops.getInt(6);
   
   /* print fetched info */
}

/* get Printers made by the manufacturer */

PreparedStatement execStat3 =
             “SELECT * FROM Printer WHERE model IN (” +
                             “SELECT model FROM Product ” +
                                 “WHERE maker = ? AND ” +
                                       “type = ‘printer’”);


execStat3.setString(1, maker);

ResultSet printers = execStat3.executeQuery();

while (printers.next()) {

   model = printers.getString(1);
   color = printers.getString(2);
   type = printers.getString(3);
   price = printers.getInt(4);

   /* print fetched info */

}
--------------------------------------------------------------------------------
e)

import java.sql.*;

int total_budget, rest_budget, pc_price, printer_price;
char pc_model[4], printer_model[4], color[6];
float min_speed;


Class.forName(“<drive name>”);

Connection myCon = 
          DriverManager.getConnection(<URL>, <username>, <password>);


/* ask user for budget & the minimum speed of pc */

/* get the cheapest PC of the minimum speed */
PreparedStatement execStat = myCon.prepareStatement( 
                                 “SELECT model, price FROM PC 
                                     WHERE speed >= ? AND price <= ?
			                             ORDER BY price”);
execStat.setFloat(1, min_speed);
execStat.setInt(2, total_budget);

ResultSet rs = execStat.executeQuery();

pc_model = rs.getString(1);
pc_price = rs.getInt(2);

if (!rs.next()) {
     printf(“no pc found within the budget\n”);
}
else {
     printf(“pc model: %s\n”, pc_model);
}

/* get Printer within the budget */
rest_budget = total_budget – pc_price;
color = “true”;

PreparedStatement execStat = myCon.prepareStatement( 
                                 “SELECT model, price FROM Printer 
                                       WHERE price <= ? AND color = ?
			                          ORDER BY price”);
execStat.setInt(1, rest_budget);
execStat.setString(2, color);

ResultSet rs = execStat.executeQuery();

print_model = rs.getString(1);
print_price = rs.getInt(2);

if (!rs.next()) {

     color = “false”;

     execStat.setInt(1, rest_budget);
     execStat.setString(2, color);

     ResultSet rs = execStat.executeQuery();

     print_model = rs.getString(1);
     print_price = rs.getInt(2);

     if (!rs.next())
        printf(“no printer found within the budget\n”);
     else
        printf(“printer model: %s\n”, printer_model);

}
else {
     printf(“printer model: %s\n”, printer_model);
}